package nl.hu.verbrandendephoenix.server;

import java.sql.Connection;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import nl.hu.verbrandendephoenix.shared.Conversation;
import nl.hu.verbrandendephoenix.shared.Message;
import nl.hu.verbrandendephoenix.shared.User;


public class ConversationMethods {
	
	public static final String DATE_FORMAT_DAY = "dd-MM-yyyy";
	public static final String DATE_FORMAT_HOUR = "HH";
	public static final String DATE_FORMAT_MIN = "mm";
	
	public static ArrayList<Conversation> getConversations(User u, Connection con) {
		int ind = u.getDatabaseIndex();
		ArrayList<Conversation> convs = new ArrayList<Conversation>();
		try {
			int index = -1;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT u.Username, c.CONTACTID, c.CID, m.text FROM MESSAGE m, Gebruiker U, CONVERSATION c WHERE m.cid = c.cid AND c.OWNERID = '" + ind + "' AND u.USERID = C.CONTACTID ORDER BY m.DAY, m.HOUR, m.MIN DESC" );
			while(rs.next())
			{
				if(index != rs.getInt("CONTACTID")) {
					User u1 = new User();
					u1.setUserName(rs.getString("USERNAME"));
					u1.setDatabaseIndex(rs.getInt("CONTACTID"));
					Conversation c = new Conversation(u1, rs.getString("TEXT"), rs.getInt("CID"));
					convs.add(c);
					index = rs.getInt("CONTACTID");
				}
			}
			stmt.close();
			con.close();
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		return convs;
	}

	public static Conversation getConversationData(int index, Connection con) {
		Conversation c = new Conversation();
		try {
			//Get User
			Statement stmt1 = con.createStatement();
			ResultSet rs1 = stmt1.executeQuery("SELECT G.* FROM GEBRUIKER G, CONVERSATION C WHERE C.CID = '" + index + "' AND C.CONTACTID = G.USERID" );
			
			//Get Messages
			Statement stmt2 = con.createStatement();
			ResultSet rs2 = stmt2.executeQuery("SELECT * FROM MESSAGE WHERE CID = '"+index+"'" );
			User u = new User();
			
				while(rs1.next()) {
					int dbi = rs1.getInt("USERID");
					String Uname = rs1.getString("USERNAME");
					String email = rs1.getString("EMAIL");
					String fname = rs1.getString("FIRSTNAME");
					String lname = rs1.getString("LASTNAME");
					String p = rs1.getString("PREFIX");
					String phone = rs1.getString("PHONEHOME");
					String city = rs1.getString("CITY");
					int g = rs1.getInt("GENDER");
					
					u.setDatabaseIndex(dbi);
					u.setUserName(Uname);
					u.setFirstName(fname);
					u.setNamePrefix(p);
					u.setLastName(lname);
					u.setGender(g);
					u.setPhoneNumberHome(phone);
					u.setCity(city);
					u.setEmail(email);
				}
				
				ArrayList<Message> messages = new ArrayList<Message>();
				while(rs2.next()) {
					int mid = rs2.getInt("MID");
					String txt = rs2.getString("TEXT");
					String d = rs2.getString("DAY");
					int hr = rs2.getInt("HOUR");
					int min = rs2.getInt("MIN");
					messages.add(new Message(mid, txt, d, hr, min));
				}

				c.setID(index);
				c.setPartner(u);
				c.setMessages(messages);

			stmt1.close();
			stmt2.close();
			con.close();
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		return c;
	}

	public static Message storeMessage(int index, String text, User current_User, User conv_partner, Connection con) {
		Message m = new Message();
		try {
			Date d = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_DAY);
			SimpleDateFormat sdf2 = new SimpleDateFormat(DATE_FORMAT_HOUR);
			SimpleDateFormat sdf3 = new SimpleDateFormat(DATE_FORMAT_MIN);
			
			String day = sdf.format(d);
			int minute = Integer.parseInt(sdf3.format(d));
			int hour = Integer.parseInt(sdf2.format(d));
			System.out.println("Day: " + day + " time: " + hour + ":" + minute);
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("INSERT INTO MESSAGE (MID, CID, TEXT, HOUR, MIN, DAY) VALUES ('', '"+ index +"', '"+ text +"', '"+ hour +"', '"+ minute +"', '"+day+"')" );

			m.setText(text);
			m.setHour(hour);
			m.setMinute(minute);
			m.setDay(day);
			
			rs.close();
			stmt.close();
			con.close();
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		
		return m;
	}
}
